package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Talent;
import com.hnl.to.wrapper.TalentCollectionPageWrapper;
import com.hnl.util.FormatUtil;

public class DBTalentDAO implements TalentDAO {
	public static final Logger LOG = LoggerFactory.getLogger(DBTalentDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;
	private PreparedStatement updateStmt;

	protected DBTalentDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	@Override
	public Talent saveTalent(Talent talent) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO TALENT (e_first_name, e_last_name, c_first_name, c_last_name, nationality,"
									+ " birthday, family_background, interest, religion, current_company,"
									+ " current_country, current_city, job_function, position, industry_level_1,"
									+ " industry_level_2, total_yrs_work, current_salary, work_experience, expertise,"
									+ " language_skill, others, career_goal, office_tel, DID, home_tel, "
									+ "cellphone_1, cellphone_2, company_email, private_email, rating, skype, LinkedIn,"
									+ " facebook, extra_comment, create_date, create_user, modify_date, modify_user, isdelete,"
									+ "overseas_exposure,plurk,twitter,marital_status,address,technical_skill,additional_reference_info,gender,fax)"
									+ " VALUES(?,?,?,?,?,?,?,?,?,?"
									+ ",?,?,?,?,?,?,?,?,?,?"
									+ ",?,?,?,?,?,?,?,?,?,?"
									+ ",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setString(1, talent.geteFirstname());
			saveStmt.setString(2, talent.geteLastname());
			saveStmt.setString(3, talent.getcFirstname());
			saveStmt.setString(4, talent.getcLastname());
			saveStmt.setInt(
					5,
					(talent.getNationality() == null || talent.getNationality() == 0) ? Integer
							.valueOf(1) : talent.getNationality());
			saveStmt.setString(6, FormatUtil.getXmlDate(talent.getBirthdate()));
			saveStmt.setString(7, talent.getFamilyBackground());
			saveStmt.setString(8, talent.getInterest());
			saveStmt.setInt(
					9,
					(talent.getReligion() == null || talent.getReligion() == 0) ? Integer
							.valueOf(1) : talent.getReligion());
			saveStmt.setString(10, talent.getCompany());
			saveStmt.setInt(
					11,
					(talent.getCountry() == null || talent.getCountry() == 0) ? Integer
							.valueOf(1) : talent.getCountry());
			saveStmt.setString(12, talent.getCity());
			saveStmt.setString(13, talent.getJobfunction());
			saveStmt.setString(14, talent.getPosition());
			saveStmt.setInt(
					15,
					(talent.getIndustry1() == null || talent.getIndustry1() == 0) ? Integer
							.valueOf(1) : talent.getIndustry1());
			saveStmt.setInt(
					16,
					(talent.getIndustry2() == null || talent.getIndustry2() == 0) ? Integer
							.valueOf(1) : talent.getIndustry2());
			saveStmt.setString(17, talent.getTotalYearsOfWork());
			saveStmt.setString(18, talent.getSalary());
			saveStmt.setString(19, talent.getWorkExperience());
			saveStmt.setString(20, talent.getExpertise());
			saveStmt.setString(21, talent.getLanguageSkill());
			saveStmt.setString(22, talent.getOthers());
			saveStmt.setString(23, talent.getCareerGoal());
			saveStmt.setString(24, talent.getOfficeTel());
			saveStmt.setString(25, talent.getDid());
			saveStmt.setString(26, talent.getHomePhone());
			saveStmt.setString(27, talent.getCell1());
			saveStmt.setString(28, talent.getCell2());
			saveStmt.setString(29, talent.getComMail());
			saveStmt.setString(30, talent.getPersonalMail());
			saveStmt.setString(31, talent.getRating());
			saveStmt.setString(32, talent.getSkype());
			saveStmt.setString(33, talent.getLinkedin());
			saveStmt.setString(34, talent.getFacebook());
			saveStmt.setString(35, talent.getComment());
			saveStmt.setString(36,
					FormatUtil.getXmlDate(talent.getCreateDate()));
			saveStmt.setString(37, talent.getCreateUser().toString());
			saveStmt.setString(38,
					FormatUtil.getXmlDate(talent.getModifyDate()));
			saveStmt.setInt(39, talent.getModifyUser());
			saveStmt.setBoolean(40, talent.getIsdelete());
			saveStmt.setString(41, talent.getOverseasExposure());
			saveStmt.setString(42, talent.getPlurk());
			saveStmt.setString(43, talent.getTwitter());
			saveStmt.setInt(44,
					(talent.getMaritalStatus() == null || talent
							.getMaritalStatus() == 0) ? Integer.valueOf(1)
							: talent.getMaritalStatus());
			saveStmt.setString(45, talent.getAddress());
			saveStmt.setString(46, talent.getTechnicalSkill());
			saveStmt.setString(47, talent.getAdditionalReferenceInfo());
			saveStmt.setInt(
					48,
					(talent.getGender() == null || talent.getGender() == 0) ? Integer
							.valueOf(1) : talent.getGender());
			saveStmt.setString(49, talent.getFax());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				talent.setTalentId(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving Talent.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talent;
	}

	@Override
	public Collection<Talent> loadAllTalent() {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Collection<Talent> talentCollection = new Vector<Talent>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_ID, e_first_name, e_last_name, c_first_name, c_last_name, "
							+ "nationality, birthday, family_background, interest, religion, "
							+ "current_company, current_country, current_city, job_function,"
							+ " position, industry_level_1, industry_level_2, total_yrs_work,"
							+ " current_salary, work_experience, expertise, language_skill, "
							+ "others, career_goal, office_tel, DID, home_tel, "
							+ "cellphone_1, cellphone_2, company_email, private_email, rating,"
							+ " skype, LinkedIn, facebook, extra_comment, create_date, create_user,"
							+ " modify_date, modify_user, isdelete,overseas_exposure,plurk,twitter,"
							+ " marital_status,address,technical_skill,additional_reference_info,gender,fax"
							+ " FROM talent WHERE isdelete=false order by modify_date desc");
			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				Talent talent = new Talent();
				talent.setTalentId(rs.getInt("talent_ID"));
				talent.seteFirstname(rs.getString("e_first_name"));
				talent.seteLastname(rs.getString("e_last_name"));
				talent.setcFirstname(rs.getString("c_first_name"));
				talent.setcLastname(rs.getString("c_last_name"));
				talent.setNationality(rs.getInt("nationality"));
				talent.setBirthdate(rs.getDate("birthday"));
				talent.setFamilyBackground(rs.getString("family_background"));
				talent.setInterest(rs.getString("interest"));
				talent.setReligion(rs.getInt("religion"));
				talent.setCompany(rs.getString("current_company"));
				talent.setCountry(rs.getInt("current_country"));
				talent.setCity(rs.getString("current_city"));
				talent.setJobfunction(rs.getString("job_function"));
				talent.setPosition(rs.getString("position"));
				talent.setIndustry1(rs.getInt("industry_level_1"));
				talent.setIndustry2(rs.getInt("industry_level_2"));
				talent.setTotalYearsOfWork(rs.getString("total_yrs_work"));
				talent.setSalary(rs.getString("current_salary"));
				talent.setWorkExperience(rs.getString("work_experience"));
				talent.setExpertise(rs.getString("expertise"));
				talent.setLanguageSkill(rs.getString("language_skill"));
				talent.setOthers(rs.getString("others"));
				talent.setCareerGoal(rs.getString("career_goal"));
				talent.setOfficeTel(rs.getString("office_tel"));
				talent.setDid(rs.getString("DID"));
				talent.setHomePhone(rs.getString("home_tel"));
				talent.setCell1(rs.getString("cellphone_1"));
				talent.setCell2(rs.getString("cellphone_2"));
				talent.setComMail(rs.getString("company_email"));
				talent.setPersonalMail(rs.getString("private_email"));
				talent.setRating(rs.getString("rating"));
				talent.setSkype(rs.getString("skype"));
				talent.setLinkedin(rs.getString("LinkedIn"));
				talent.setFacebook(rs.getString("facebook"));
				talent.setComment(rs.getString("extra_comment"));
				talent.setCreateDate(rs.getDate("create_date"));
				talent.setCreateUser(rs.getInt("create_user"));
				talent.setModifyDate(rs.getDate("modify_date"));
				talent.setModifyUser(rs.getInt("modify_user"));
				talent.setIsdelete(rs.getBoolean("isdelete"));
				talent.setOverseasExposure(rs.getString("overseas_exposure"));
				talent.setPlurk(rs.getString("plurk"));
				talent.setTwitter(rs.getString("twitter"));
				talent.setMaritalStatus(rs.getInt("marital_status"));
				talent.setAddress(rs.getString("address"));
				talent.setTechnicalSkill(rs.getString("technical_skill"));
				talent.setAdditionalReferenceInfo(rs
						.getString("additional_reference_info"));
				talent.setGender(rs.getInt("gender"));
				talent.setFax(rs.getNString("fax"));
				talentCollection.add(talent);
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Talent.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talentCollection;
	}

	public Collection<Talent> loadTalentByUserId(Integer uid) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Collection<Talent> talentCollection = new Vector<Talent>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_ID,e_first_name, e_last_name, c_first_name, c_last_name, "
							+ "nationality, birthday, family_background, interest, religion, "
							+ "current_company, current_country, current_city, job_function,"
							+ " position, industry_level_1, industry_level_2, total_yrs_work,"
							+ " current_salary, work_experience, expertise, language_skill, "
							+ "others, career_goal, office_tel, DID, home_tel, "
							+ "cellphone_1, cellphone_2, company_email, private_email, rating,"
							+ " skype, LinkedIn, facebook, extra_comment, create_date, create_user,"
							+ " modify_date, modify_user, isdelete,overseas_exposure,plurk,twitter,"
							+ "marital_status,address,technical_skill,additional_reference_info,gender,fax"
							+ " FROM talent WHERE isdelete=false and modify_user=? order by modify_date desc");
			loadStmt.setInt(1, uid);
			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				Talent talent = new Talent();
				talent.setTalentId(rs.getInt("talent_ID"));
				talent.seteFirstname(rs.getString("e_first_name"));
				talent.seteLastname(rs.getString("e_last_name"));
				talent.setcFirstname(rs.getString("c_first_name"));
				talent.setcLastname(rs.getString("c_last_name"));
				talent.setNationality(rs.getInt("nationality"));
				talent.setBirthdate(rs.getDate("birthday"));
				talent.setFamilyBackground(rs.getString("family_background"));
				talent.setInterest(rs.getString("interest"));
				talent.setReligion(rs.getInt("religion"));
				talent.setCompany(rs.getString("current_company"));
				talent.setCountry(rs.getInt("current_country"));
				talent.setCity(rs.getString("current_city"));
				talent.setJobfunction(rs.getString("job_function"));
				talent.setPosition(rs.getString("position"));
				talent.setIndustry1(rs.getInt("industry_level_1"));
				talent.setIndustry2(rs.getInt("industry_level_2"));
				talent.setTotalYearsOfWork(rs.getString("total_yrs_work"));
				talent.setSalary(rs.getString("current_salary"));
				talent.setWorkExperience(rs.getString("work_experience"));
				talent.setExpertise(rs.getString("expertise"));
				talent.setLanguageSkill(rs.getString("language_skill"));
				talent.setOthers(rs.getString("others"));
				talent.setCareerGoal(rs.getString("career_goal"));
				talent.setOfficeTel(rs.getString("office_tel"));
				talent.setDid(rs.getString("DID"));
				talent.setHomePhone(rs.getString("home_tel"));
				talent.setCell1(rs.getString("cellphone_1"));
				talent.setCell2(rs.getString("cellphone_2"));
				talent.setComMail(rs.getString("company_email"));
				talent.setPersonalMail(rs.getString("private_email"));
				talent.setRating(rs.getString("rating"));
				talent.setSkype(rs.getString("skype"));
				talent.setLinkedin(rs.getString("LinkedIn"));
				talent.setFacebook(rs.getString("facebook"));
				talent.setComment(rs.getString("extra_comment"));
				talent.setCreateDate(rs.getDate("create_date"));
				talent.setCreateUser(rs.getInt("create_user"));
				talent.setModifyDate(rs.getDate("modify_date"));
				talent.setModifyUser(rs.getInt("modify_user"));
				talent.setIsdelete(rs.getBoolean("isdelete"));
				talent.setOverseasExposure(rs.getString("overseas_exposure"));
				talent.setPlurk(rs.getString("plurk"));
				talent.setTwitter(rs.getString("twitter"));
				talent.setMaritalStatus(rs.getInt("marital_status"));
				talent.setAddress(rs.getString("address"));
				talent.setTechnicalSkill(rs.getString("technical_skill"));
				talent.setAdditionalReferenceInfo(rs
						.getString("additional_reference_info"));
				talent.setGender(rs.getInt("gender"));
				talent.setFax(rs.getString("fax"));
				talentCollection.add(talent);
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Talent.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talentCollection;
	}

	@Override
	public Talent loadTalent(Integer talentId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Talent talent = new Talent();
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_ID,e_first_name, e_last_name, c_first_name, c_last_name,"
							+ " nationality, birthday, gender, family_background, interest, religion,"
							+ " current_company, current_country, current_city, job_function,"
							+ " position, industry_level_1, industry_level_2, total_yrs_work, "
							+ "current_salary, work_experience, overseas_exposure, expertise,"
							+ " technical_skill, language_skill, others, career_goal,"
							+ " office_tel, DID, address, marital_status, home_tel, cellphone_1, "
							+ "cellphone_2, company_email, private_email, rating, skype, LinkedIn, "
							+ "facebook, plurk, twitter, additional_reference_info, extra_comment,"
							+ " create_date, create_user, modify_date, modify_user, isdelete, fax"
							+ " FROM TALENT WHERE talent_ID=?");
			loadStmt.setInt(1, talentId);
			loadStmt.execute();
			ResultSet rs = loadStmt.getResultSet();
			while (rs.next()) {
				talent.setTalentId(rs.getInt("talent_ID"));
				talent.seteFirstname(rs.getString("e_first_name"));
				talent.seteLastname(rs.getString("e_last_name"));
				talent.setcFirstname(rs.getString("c_first_name"));
				talent.setcLastname(rs.getString("c_last_name"));
				talent.setNationality(rs.getInt("nationality"));
				talent.setBirthdate(rs.getDate("birthday"));
				talent.setFamilyBackground(rs.getString("family_background"));
				talent.setInterest(rs.getString("interest"));
				talent.setReligion(rs.getInt("religion"));
				talent.setCompany(rs.getString("current_company"));
				talent.setCountry(rs.getInt("current_country"));
				talent.setCity(rs.getString("current_city"));
				talent.setJobfunction(rs.getString("job_function"));
				talent.setPosition(rs.getString("position"));
				talent.setIndustry1(rs.getInt("industry_level_1"));
				talent.setIndustry2(rs.getInt("industry_level_2"));
				talent.setTotalYearsOfWork(rs.getString("total_yrs_work"));
				talent.setSalary(rs.getString("current_salary"));
				talent.setWorkExperience(rs.getString("work_experience"));
				talent.setExpertise(rs.getString("expertise"));
				talent.setLanguageSkill(rs.getString("language_skill"));
				talent.setOthers(rs.getString("others"));
				talent.setCareerGoal(rs.getString("career_goal"));
				talent.setOfficeTel(rs.getString("office_tel"));
				talent.setDid(rs.getString("DID"));
				talent.setHomePhone(rs.getString("home_tel"));
				talent.setCell1(rs.getString("cellphone_1"));
				talent.setCell2(rs.getString("cellphone_2"));
				talent.setComMail(rs.getString("company_email"));
				talent.setPersonalMail(rs.getString("private_email"));
				talent.setRating(rs.getString("rating"));
				talent.setSkype(rs.getString("skype"));
				talent.setLinkedin(rs.getString("LinkedIn"));
				talent.setFacebook(rs.getString("facebook"));
				talent.setComment(rs.getString("extra_comment"));
				talent.setCreateDate(rs.getDate("create_date"));
				talent.setCreateUser(rs.getInt("create_user"));
				talent.setModifyDate(rs.getDate("modify_date"));
				talent.setModifyUser(rs.getInt("modify_user"));
				talent.setIsdelete(rs.getBoolean("isdelete"));
				talent.setOverseasExposure(rs.getString("overseas_exposure"));
				talent.setPlurk(rs.getString("plurk"));
				talent.setTwitter(rs.getString("twitter"));
				talent.setMaritalStatus(rs.getInt("marital_status"));
				talent.setAddress(rs.getString("address"));
				talent.setTechnicalSkill(rs.getString("technical_skill"));
				talent.setAdditionalReferenceInfo(rs
						.getString("additional_reference_info"));
				talent.setGender(rs.getInt("gender"));
				talent.setFax(rs.getString("fax"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Talent.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talent;
	}

	@Override
	public void updateTalent(Talent talent) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			updateStmt = conn
					.prepareStatement("UPDATE TALENT SET e_first_name=?, e_last_name=?, c_first_name=?, c_last_name=?, "
							+ "nationality=?, birthday=?, family_background=?, interest=?, religion=?, "
							+ "current_company=?, current_country=?, current_city=?, job_function=?,"
							+ " position=?, industry_level_1=?, industry_level_2=?, total_yrs_work=?,"
							+ " current_salary=?, work_experience=?, expertise=?, language_skill=?, "
							+ "others=?, career_goal=?, office_tel=?, DID=?, home_tel=?, "
							+ "cellphone_1=?, cellphone_2=?, company_email=?, private_email=?, rating=?,"
							+ " skype=?, LinkedIn=?, facebook=?, extra_comment=?,"
							+ " modify_date=?, modify_user=?, isdelete=?,overseas_exposure=?,plurk=?,twitter=?,"
							+ "marital_status=?,address=?,technical_skill=?,additional_reference_info=?,gender=?,fax=? WHERE talent_ID=?");
			updateStmt.setString(1, talent.geteFirstname());
			updateStmt.setString(2, talent.geteLastname());
			updateStmt.setString(3, talent.getcFirstname());
			updateStmt.setString(4, talent.getcLastname());
			updateStmt
					.setInt(5, (talent.getNationality() == null || talent
							.getNationality() == 0) ? Integer.valueOf(1)
							: talent.getNationality());
			updateStmt.setString(6,
					FormatUtil.getXmlDate(talent.getBirthdate()));
			updateStmt.setString(7, talent.getFamilyBackground());
			updateStmt.setString(8, talent.getInterest());
			updateStmt
					.setInt(9,
							(talent.getReligion() == null || talent
									.getReligion() == 0) ? Integer.valueOf(1)
									: talent.getReligion());
			updateStmt.setString(10, talent.getCompany());
			updateStmt
					.setInt(11,
							(talent.getCountry() == null || talent.getCountry() == 0) ? Integer
									.valueOf(1) : talent.getCountry());
			updateStmt.setString(12, talent.getCity());
			updateStmt.setString(13, talent.getJobfunction());
			updateStmt.setString(14, talent.getPosition());
			updateStmt
					.setInt(15,
							(talent.getIndustry1() == null || talent
									.getIndustry1() == 0) ? Integer.valueOf(1)
									: talent.getIndustry1());
			updateStmt
					.setInt(16,
							(talent.getIndustry2() == null || talent
									.getIndustry2() == 0) ? Integer.valueOf(1)
									: talent.getIndustry2());
			updateStmt.setString(17, talent.getTotalYearsOfWork());
			updateStmt.setString(18, talent.getSalary());
			updateStmt.setString(19, talent.getWorkExperience());
			updateStmt.setString(20, talent.getExpertise());
			updateStmt.setString(21, talent.getLanguageSkill());
			updateStmt.setString(22, talent.getOthers());
			updateStmt.setString(23, talent.getCareerGoal());
			updateStmt.setString(24, talent.getOfficeTel());
			updateStmt.setString(25, talent.getDid());
			updateStmt.setString(26, talent.getHomePhone());
			updateStmt.setString(27, talent.getCell1());
			updateStmt.setString(28, talent.getCell2());
			updateStmt.setString(29, talent.getComMail());
			updateStmt.setString(30, talent.getPersonalMail());
			updateStmt.setString(31, talent.getRating());
			updateStmt.setString(32, talent.getSkype());
			updateStmt.setString(33, talent.getLinkedin());
			updateStmt.setString(34, talent.getFacebook());
			updateStmt.setString(35, talent.getComment());
			updateStmt.setString(36,
					FormatUtil.getXmlDate(talent.getModifyDate()));
			updateStmt.setInt(37, talent.getModifyUser());
			updateStmt.setBoolean(38, talent.getIsdelete());
			updateStmt.setString(39, talent.getOverseasExposure());
			updateStmt.setString(40, talent.getPlurk());
			updateStmt.setString(41, talent.getTwitter());
			updateStmt.setInt(42, talent.getMaritalStatus());
			updateStmt.setString(43, talent.getAddress());
			updateStmt.setString(44, talent.getTechnicalSkill());
			updateStmt.setString(45, talent.getAdditionalReferenceInfo());
			updateStmt.setInt(46, talent.getGender());
			updateStmt.setString(47, talent.getFax());
			updateStmt.setInt(48, talent.getTalentId());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error updating Talent.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	@Override
	public void deleteTalent(Integer talentId) {
		Talent talent = loadTalent(talentId);
		talent.setIsdelete(true);
		updateTalent(talent);
	}

	@Override
	public Collection<Talent> loadAllTalentLikeTerm(String term) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Collection<Talent> talentCollection = new Vector<Talent>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_ID, e_first_name, e_last_name, c_first_name, c_last_name, "
							+ "nationality, birthday, family_background, interest, religion, "
							+ "current_company, current_country, current_city, job_function,"
							+ " position, industry_level_1, industry_level_2, total_yrs_work,"
							+ " current_salary, work_experience, expertise, language_skill, "
							+ "others, career_goal, office_tel, DID, home_tel, "
							+ "cellphone_1, cellphone_2, company_email, private_email, rating,"
							+ " skype, LinkedIn, facebook, extra_comment, create_date, create_user,"
							+ " modify_date, modify_user, isdelete,overseas_exposure,plurk,twitter,"
							+ " marital_status,address,technical_skill,additional_reference_info,gender,fax"
							+ " FROM talent WHERE isdelete=false AND CONCAT_WS(\" \", e_first_name, e_last_name, c_first_name, c_last_name) LIKE '%"
							+ term + "%'" + " order by modify_date desc");
			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				Talent talent = new Talent();
				talent.setTalentId(rs.getInt("talent_ID"));
				talent.seteFirstname(rs.getString("e_first_name"));
				talent.seteLastname(rs.getString("e_last_name"));
				talent.setcFirstname(rs.getString("c_first_name"));
				talent.setcLastname(rs.getString("c_last_name"));
				talent.setNationality(rs.getInt("nationality"));
				talent.setBirthdate(rs.getDate("birthday"));
				talent.setFamilyBackground(rs.getString("family_background"));
				talent.setInterest(rs.getString("interest"));
				talent.setReligion(rs.getInt("religion"));
				talent.setCompany(rs.getString("current_company"));
				talent.setCountry(rs.getInt("current_country"));
				talent.setCity(rs.getString("current_city"));
				talent.setJobfunction(rs.getString("job_function"));
				talent.setPosition(rs.getString("position"));
				talent.setIndustry1(rs.getInt("industry_level_1"));
				talent.setIndustry2(rs.getInt("industry_level_2"));
				talent.setTotalYearsOfWork(rs.getString("total_yrs_work"));
				talent.setSalary(rs.getString("current_salary"));
				talent.setWorkExperience(rs.getString("work_experience"));
				talent.setExpertise(rs.getString("expertise"));
				talent.setLanguageSkill(rs.getString("language_skill"));
				talent.setOthers(rs.getString("others"));
				talent.setCareerGoal(rs.getString("career_goal"));
				talent.setOfficeTel(rs.getString("office_tel"));
				talent.setDid(rs.getString("DID"));
				talent.setHomePhone(rs.getString("home_tel"));
				talent.setCell1(rs.getString("cellphone_1"));
				talent.setCell2(rs.getString("cellphone_2"));
				talent.setComMail(rs.getString("company_email"));
				talent.setPersonalMail(rs.getString("private_email"));
				talent.setRating(rs.getString("rating"));
				talent.setSkype(rs.getString("skype"));
				talent.setLinkedin(rs.getString("LinkedIn"));
				talent.setFacebook(rs.getString("facebook"));
				talent.setComment(rs.getString("extra_comment"));
				talent.setCreateDate(rs.getDate("create_date"));
				talent.setCreateUser(rs.getInt("create_user"));
				talent.setModifyDate(rs.getDate("modify_date"));
				talent.setModifyUser(rs.getInt("modify_user"));
				talent.setIsdelete(rs.getBoolean("isdelete"));
				talent.setOverseasExposure(rs.getString("overseas_exposure"));
				talent.setPlurk(rs.getString("plurk"));
				talent.setTwitter(rs.getString("twitter"));
				talent.setMaritalStatus(rs.getInt("marital_status"));
				talent.setAddress(rs.getString("address"));
				talent.setTechnicalSkill(rs.getString("technical_skill"));
				talent.setAdditionalReferenceInfo(rs
						.getString("additional_reference_info"));
				talent.setGender(rs.getInt("gender"));
				talent.setFax(rs.getNString("fax"));
				talentCollection.add(talent);
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Talent using like term.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talentCollection;

	}

	@Override
	public TalentCollectionPageWrapper loadTalentByPageQuery(String query) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		TalentCollectionPageWrapper talentCollectionPageWrapper = new TalentCollectionPageWrapper();

		try {
			PreparedStatement exeStmt = conn.prepareStatement(query);
			ResultSet rs = exeStmt.executeQuery();
			exeStmt = conn.prepareStatement("SELECT FOUND_ROWS()");
			ResultSet rResultFilterTotal = exeStmt.executeQuery();
			rResultFilterTotal.next();
			talentCollectionPageWrapper.setFilteredTotal(rResultFilterTotal
					.getInt(1));

			/* Total data set length */
			query = "SELECT COUNT(talent_ID) FROM TALENT WHERE isdelete=false";
			exeStmt = conn.prepareStatement(query);
			rResultFilterTotal = exeStmt.executeQuery();
			rResultFilterTotal.next();
			talentCollectionPageWrapper.setTotal(rResultFilterTotal.getInt(1));

			while (rs.next()) {
				Talent talent = new Talent();
				talent.setTalentId(rs.getInt("talent_ID"));
				talent.seteFirstname(rs.getString("e_first_name"));
				talent.seteLastname(rs.getString("e_last_name"));
				talent.setcFirstname(rs.getString("c_first_name"));
				talent.setcLastname(rs.getString("c_last_name"));
				talent.setNationality(rs.getInt("nationality"));
				talent.setBirthdate(rs.getDate("birthday"));
				talent.setFamilyBackground(rs.getString("family_background"));
				talent.setInterest(rs.getString("interest"));
				talent.setReligion(rs.getInt("religion"));
				talent.setCompany(rs.getString("current_company"));
				talent.setCountry(rs.getInt("current_country"));
				talent.setCity(rs.getString("current_city"));
				talent.setJobfunction(rs.getString("job_function"));
				talent.setPosition(rs.getString("position"));
				talent.setIndustry1(rs.getInt("industry_level_1"));
				talent.setIndustry2(rs.getInt("industry_level_2"));
				talent.setTotalYearsOfWork(rs.getString("total_yrs_work"));
				talent.setSalary(rs.getString("current_salary"));
				talent.setWorkExperience(rs.getString("work_experience"));
				talent.setExpertise(rs.getString("expertise"));
				talent.setLanguageSkill(rs.getString("language_skill"));
				talent.setOthers(rs.getString("others"));
				talent.setCareerGoal(rs.getString("career_goal"));
				talent.setOfficeTel(rs.getString("office_tel"));
				talent.setDid(rs.getString("DID"));
				talent.setHomePhone(rs.getString("home_tel"));
				talent.setCell1(rs.getString("cellphone_1"));
				talent.setCell2(rs.getString("cellphone_2"));
				talent.setComMail(rs.getString("company_email"));
				talent.setPersonalMail(rs.getString("private_email"));
				talent.setRating(rs.getString("rating"));
				talent.setSkype(rs.getString("skype"));
				talent.setLinkedin(rs.getString("LinkedIn"));
				talent.setFacebook(rs.getString("facebook"));
				talent.setComment(rs.getString("extra_comment"));
				talent.setCreateDate(rs.getDate("create_date"));
				talent.setCreateUser(rs.getInt("create_user"));
				talent.setModifyDate(rs.getDate("modify_date"));
				talent.setModifyUser(rs.getInt("modify_user"));
				talent.setIsdelete(rs.getBoolean("isdelete"));
				talent.setOverseasExposure(rs.getString("overseas_exposure"));
				talent.setPlurk(rs.getString("plurk"));
				talent.setTwitter(rs.getString("twitter"));
				talent.setMaritalStatus(rs.getInt("marital_status"));
				talent.setAddress(rs.getString("address"));
				talent.setTechnicalSkill(rs.getString("technical_skill"));
				talent.setAdditionalReferenceInfo(rs
						.getString("additional_reference_info"));
				talent.setGender(rs.getInt("gender"));
				talent.setFax(rs.getNString("fax"));
				talentCollectionPageWrapper.getTalentCollection().add(talent);
			}
			rs.close();
			exeStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Talent by page request.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talentCollectionPageWrapper;
	}

}
